package net.hn.hnms.biz.plan.mapper;

import net.hn.hnms.biz.plan.vo.*;
import net.hn.hnms.biz.plan.vo.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface GroupBigScreenMapper {



    @Select("<script>" +
            "   SELECT \n" +
            "    org_full_name AS orgFullName,     " +
            //月报和年报用总计划
            "            <if test='type== 2 or type== 3'>" +
            "    ROUND(SUM(total_planned_output_qty) / 10000.0, 2) AS plannedOutputQty," +
            "    ROUND(SUM(total_planned_sales_qty) / 10000.0, 2) AS plannedSalesQty," +
            "    ROUND(SUM(total_planned_peel_qty) / 10000.0, 2) AS plannedPeelQty," +
            "    ROUND(SUM(total_planned_footage_qty), 2) AS plannedFootageQty," +
            "    ROUND(AVG(total_output_compl_rate) * 100, 2) AS outputComplRate,   \n" +
            "    ROUND(AVG(total_sales_compl_rate) * 100, 2) AS salesComplRate,     \n" +
            "    ROUND(AVG(total_footage_compl_rate) * 100, 2) AS footageComplRate,\n" +
            "    ROUND(AVG(total_peel_compl_rate) * 100, 2) AS peelComplRate,   " +
            "            </if>" +
            //日报还用进度计划字段
            "            <if test='type== 1'>" +
            "     TO_CHAR(ROUND(SUM(planned_output_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedOutputQty,\n" +
            "    TO_CHAR(ROUND(SUM(planned_sales_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedSalesQty,\n" +
            "    TO_CHAR(ROUND(SUM(planned_peel_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedPeelQty,\n" +
            "    TO_CHAR(ROUND(SUM(planned_footage_qty), 2), 'FM9999999990.00') AS plannedFootageQty,\n" +
            "    TO_CHAR(ROUND(AVG(output_compl_rate) * 100, 2), 'FM9999999990.00') AS outputComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(sales_compl_rate) * 100, 2), 'FM9999999990.00') AS salesComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(footage_compl_rate) * 100, 2), 'FM9999999990.00') AS footageComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(peel_compl_rate) * 100, 2), 'FM9999999990.00') AS peelComplRate,  " +
            "            </if>" +
            "    TO_CHAR(TRUNC(SUM(actual_output_qty) / 10000.0, 2), 'FM9999999990.00') AS actualOutputQty,\n" +
            "    TO_CHAR(TRUNC(SUM(actual_sales_qty) / 10000.0, 2), 'FM9999999990.00') AS actualSalesQty,\n" +
            "    TO_CHAR(TRUNC(SUM(actual_peel_qty) / 10000.0, 2), 'FM9999999990.00') AS actualPeelQty,\n" +
            "    TO_CHAR(TRUNC(SUM(actual_footage_qty), 2), 'FM9999999990.00') AS actualFootageQty " +
            "FROM \n" +
            "            <if test='type== 1'>" +
            "    dm_jygl_coal_mining_daily_total_d_f\n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    dm_jygl_coal_mining_month_total_d_f\n" +
            "            </if>" +
            "            <if test='type== 3'>" +
            "    dm_jygl_coal_mining_year_total_d_f\n" +
            "            </if>" +
            "WHERE \n" +
            "    organization_type = 'MTCY' " +
            "     " +
            "            <if test='type== 1 and mark == 1' >" +
            "    and st_date <![CDATA[ = ]]>   current_date-1   " +
            "            </if>" +
            "            <if test='type== 1 and mark == 2' >" +
            "    and st_date <![CDATA[ = ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "      AND st_month = TO_CHAR(current_date-1, 'YYYY-MM') " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "       AND st_year = TO_CHAR(current_date-1, 'YYYY') " +
            "            </if>" +
            " GROUP BY \n" +
            "    org_full_name\n" +
            "ORDER BY \n" +
            "    org_full_name; " +
            "</script>")
    CoalMiningTotalVo getDailyOutputStatus(@Param("type") Integer type, @Param("mark") Integer mark);

    @Select("<script>" +
            " SELECT\n" +
            "    info.mq_rank, info.mq_full_name,\n" +
            "    info.mq_short_name as orgShortName,\n" +
            "    info.mq_code,\n" +
            "    total.org_full_name AS orgFullName,\n" +
            //月报和年报用总计划
            "            <if test='type== 2 or type== 3'>" +
            "    ROUND(SUM(total.total_planned_output_qty) / 10000.0, 2) AS plannedOutputQty," +
            "    ROUND(SUM(total.total_planned_sales_qty) / 10000.0, 2) AS plannedSalesQty," +
            "    ROUND(SUM(total.total_planned_peel_qty) / 10000.0, 2) AS plannedPeelQty," +
            "    ROUND(SUM(total.total_planned_footage_qty), 2) AS plannedFootageQty," +
            "    ROUND(AVG(total.total_output_compl_rate) * 100, 2) AS outputComplRate,   \n" +
            "    ROUND(AVG(total.total_sales_compl_rate) * 100, 2) AS salesComplRate,     \n" +
            "    ROUND(AVG(total.total_footage_compl_rate) * 100, 2) AS footageComplRate,\n" +
            "    ROUND(AVG(total.total_peel_compl_rate) * 100, 2) AS peelComplRate,   " +
            "            </if>" +
            //日报还用进度计划字段
            "            <if test='type== 1'>" +
            "     TO_CHAR(ROUND(SUM(total.planned_output_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedOutputQty,\n" +
            "    TO_CHAR(ROUND(SUM(total.planned_sales_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedSalesQty,\n" +
            "    TO_CHAR(ROUND(SUM(total.planned_peel_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedPeelQty,\n" +
            "    TO_CHAR(ROUND(SUM(total.planned_footage_qty), 2), 'FM9999999990.00') AS plannedFootageQty,\n" +
            "    TO_CHAR(ROUND(AVG(total.output_compl_rate) * 100, 2), 'FM9999999990.00') AS outputComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(total.sales_compl_rate) * 100, 2), 'FM9999999990.00') AS salesComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(total.footage_compl_rate) * 100, 2), 'FM9999999990.00') AS footageComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(total.peel_compl_rate) * 100, 2), 'FM9999999990.00') AS peelComplRate,  " +
            "            </if>" +
            "    TO_CHAR(TRUNC(SUM(total.actual_output_qty) / 10000.0, 2), 'FM9999999990.00') AS actualOutputQty,\n" +
            "    TO_CHAR(TRUNC(SUM(total.actual_sales_qty) / 10000.0, 2), 'FM9999999990.00') AS actualSalesQty,\n" +
            "    TO_CHAR(TRUNC(SUM(total.actual_peel_qty) / 10000.0, 2), 'FM9999999990.00') AS actualPeelQty,\n" +
            "    TO_CHAR(TRUNC(SUM(total.actual_footage_qty), 2), 'FM9999999990.00') AS actualFootageQty " +
//
//            "    ROUND(SUM(total.planned_output_qty) / 10000.0, 2) AS plannedOutputQty,\n" +
//            "    ROUND(SUM(total.planned_sales_qty) / 10000.0, 2) AS plannedSalesQty,\n" +
//            "    ROUND(SUM(total.planned_peel_qty) / 10000.0, 2) AS plannedPeelQty,\n" +
//            "    ROUND(SUM(total.planned_footage_qty), 2) AS plannedFootageQty,\n" +
//            "    ROUND(AVG(total.output_compl_rate) * 100, 2) AS outputComplRate,\n" +
//            "    ROUND(AVG(total.sales_compl_rate) * 100, 2) AS salesComplRate,\n" +
//            "    ROUND(AVG(total.footage_compl_rate) * 100, 2) AS footageComplRate,\n" +
//            "    ROUND(AVG(total.peel_compl_rate) * 100, 2) AS peelComplRate,\n" +
//            "    TRUNC(SUM(total.actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
//            "    TRUNC(SUM(total.actual_sales_qty) / 10000.0, 2) AS actualSalesQty,\n" +
//            "    TRUNC(SUM(total.actual_peel_qty) / 10000.0, 2) AS actualPeelQty,\n" +
//            "    TRUNC(SUM(total.actual_footage_qty), 2) AS actualFootageQty\n" +
            "FROM\n" +
            "   \t\n" +
            "\t(SELECT mq_full_name,  mq_short_name,mq_rank,mq_code  FROM dim.dim_coal_mine_basic_info where mq_short_name  not in('北能公司','伊敏煤电','北方公司','蒙东公司')  GROUP BY mq_rank,mq_full_name,mq_short_name,mq_code )  info\n" +
            "LEFT JOIN\n" +
            "            <if test='type== 1'>" +
            "    dm.dm_jygl_coal_mining_daily_total_d_f total ON info.mq_code = total.org_code\n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    dm.dm_jygl_coal_mining_month_detail_d_f total ON info.mq_code = total.org_code\n" +
            "            </if>" +
            "            <if test='type== 3'>" +
            "    dm.dm_jygl_coal_mining_year_total_d_f total ON info.mq_code = total.org_code\n" +
            "            </if>" +
            "WHERE \n" +
            "    total.organization_type = 'MQ'\n" +
            "   and total.org_code = #{orgCode}  " +
            "            <if test='type== 1 and mark == 1' >" +
            "    and total.st_date <![CDATA[ = ]]>   current_date-1  " +
            "            </if>" +
            "            <if test='type== 1 and mark == 2' >" +
            "    and total.st_date <![CDATA[ = ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "      AND total.st_month = TO_CHAR(current_date-1, 'YYYY-MM') " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "       AND total.st_year = TO_CHAR(current_date-1, 'YYYY') " +
            "            </if>" +
            "GROUP BY\n" +
            "    info.mq_rank,total.org_full_name,\n" +
            "    info.mq_full_name,\n" +
            "    info.mq_short_name,\n" +
            "    info.mq_code\n" +
            "ORDER BY\n" +
            "    info.mq_rank, total.org_full_name,\n" +
            "    info.mq_full_name,\n" +
            "    info.mq_short_name,\n" +
            "    info.mq_code; " +
            "</script>")
    CoalMiningTotalVo getDailyOutputStatusByOrgCode(@Param("type") Integer type,@Param("mark") Integer mark,@Param("orgCode") String orgCode);


    @Select("<script>" +
            "   SELECT \n" +
            "    org_full_name AS orgFullName,     " +
            "  \n" +
            "    TRUNC(AVG(output_compl_rate)  * 100, 2) AS outputComplRate,   " +
            "   \n" +
            "    TRUNC(AVG(sales_compl_rate)  * 100, 2) AS salesComplRate,     " +
            "   \n" +
            "    TRUNC(AVG(footage_compl_rate)  * 100, 2) AS footageComplRate , " +
            "    TRUNC(AVG(peel_compl_rate)  * 100, 2) AS peelComplRate " +
            "FROM \n" +
            " dim.dim_coal_mine_basic_info  info left join  " +

            "            <if test='type== 1'>" +
            "    dm.dm_jygl_coal_mining_daily_total_d_f d  \n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    dm.dm_jygl_coal_mining_month_total_d_f d\n" +
            "            </if>" +
            "            <if test='type== 3'>" +
            "    dm.dm_jygl_coal_mining_year_total_d_f d\n" +
            "            </if>" +
            " on info.mk_full_name = d.org_full_name  " +

            "WHERE \n" +
            "    d.organization_type = 'MK'   and d.org_short_name  not in('刘园子','马蹄沟')   " +
            "            <if test='flag== 3'>" +
            " and  info.mining_method_types='井工矿'  \n" +
            "            </if>" +
            "            <if test='flag== 4'>" +
            " and  info.mining_method_types='露天矿'  \n" +
            "            </if>" +
            "    " +
            "            <if test='type== 1 and mark ==1'>" +
            "    and st_date <![CDATA[ = ]]>   current_date-1    " +
            "            </if>" +
            "            <if test='type== 1 and mark ==2'>" +
            "    and st_date <![CDATA[ = ]]>   current_date-2   " +
            "            </if>" +

            "            <if test='type== 2'>" +
            "      AND st_month = TO_CHAR(current_date-1, 'YYYY-MM') " +
            "            </if>" +
//            "            <if test='type== 2 and mark ==2'>" +
//            "      AND st_month = TO_CHAR(current_date-2, 'YYYY-MM') " +
//            "            </if>" +
            "            <if test='type== 3'>" +
            "       AND st_year = TO_CHAR(current_date-1, 'YYYY') " +
            "            </if>" +
//            "            <if test='type== 3  and mark ==2'>" +
//            "       AND st_year = TO_CHAR(current_date-2, 'YYYY') " +
//            "            </if>" +
            "GROUP BY \n" +
            "    org_full_name\n" +
            "ORDER BY \n" +
            "            <if test='flag== 1'>" +
            "     outputComplRate desc;" +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "    salesComplRate desc;" +
            "            </if>" +
            "            <if test='flag== 3'>" +
            "    footageComplRate desc;" +
            "            </if>" +
            "            <if test='flag== 4'>" +
            "    peelComplRate desc;" +
            "            </if>" +
            "    " +
            "</script>")
    List<CoalMiningTotalVo> getCompletionRateRanking(@Param("type") Integer type,@Param("flag") Integer flag,@Param("mark") Integer mark);



    @Select("<script>" +
            "   SELECT \n" +

            "            <if test='type== 1'>" +
            "    d.org_full_name AS orgFullName,     \n" +
            "         CASE \n" +
            "        WHEN d.org_full_name = '' OR d.org_full_name IS NULL THEN d.org_full_name\n" +
            "        WHEN d.org_full_name = '煤炭产业' THEN '总计'\n" +
            "        ELSE d.org_short_name\n" +
            "    END AS orgShortName,         \n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "      CASE \n" +
            "        WHEN d.org_full_name = '' OR d.org_full_name IS NULL THEN d.org_full_name\n" +
            "        WHEN d.org_short_name = '煤业公司' THEN '总计' " +
            "        WHEN d.org_short_name = '华能煤业' THEN '总计' " +
            "         WHEN d.org_full_name = '煤业公司' THEN '总计'\n" +
            "        ELSE d.org_short_name\n" +
            "    END AS orgShortName,       \n" +
            "            </if>" +
            "            <if test='type== 3'>" +
            "    b.mk_full_name AS orgFullName,     \n" +
            "    b.mk_short_name AS orgShortName,     \n" +
            "            </if>" +
            "" +
            "     TO_CHAR(TRUNC(SUM(d.actual_output_qty) / 10000, 2), 'FM9999999990.00') AS actualOutputQty,\n" +
            "    TO_CHAR(ROUND(SUM(d.planned_output_qty) / 10000, 2), 'FM9999999990.00') AS plannedOutputQty,\n" +
            "    TO_CHAR(TRUNC(SUM(d.actual_sales_qty) / 10000, 2), 'FM9999999990.00') AS actualSalesQty,\n" +
            "    TO_CHAR(ROUND(SUM(d.planned_sales_qty) / 10000, 2), 'FM9999999990.00') AS plannedSalesQty,\n" +
            "    TO_CHAR(TRUNC(SUM(d.actual_footage_qty), 2), 'FM9999999990.00') AS actualFootageQty,\n" +
            "    TO_CHAR(ROUND(SUM(d.planned_footage_qty), 2), 'FM9999999990.00') AS plannedFootageQty,\n" +
            "    TO_CHAR(TRUNC(SUM(d.actual_peel_qty) / 10000, 2), 'FM9999999990.00') AS actualPeelQty,\n" +
            "    TO_CHAR(ROUND(SUM(d.planned_peel_qty) / 10000, 2), 'FM9999999990.00') AS plannedPeelQty " +

//            "      TRUNC(SUM(d.actual_output_qty) / 10000, 2) AS actualOutputQty,    \n" +
//            "    ROUND(SUM(d.planned_output_qty) / 10000, 2) AS plannedOutputQty,  \n" +
//            "    TRUNC(SUM(d.actual_sales_qty) / 10000, 2) AS actualSalesQty,      \n" +
//            "    ROUND(SUM(d.planned_sales_qty) / 10000, 2) AS plannedSalesQty,    \n" +
//            "    TRUNC(SUM(d.actual_footage_qty), 2) AS actualFootageQty,  \n" +
//            "    ROUND(SUM(d.planned_footage_qty), 2) AS plannedFootageQty,\n" +
//            "    TRUNC(SUM(d.actual_peel_qty) / 10000, 2) AS actualPeelQty,  \n" +
//            "    ROUND(SUM(d.planned_peel_qty) / 10000, 2) AS plannedPeelQty" +
            " FROM \n" +

            "            <if test='type== 3'>" +
            "    (SELECT mk_full_name,  mk_short_name,mk_rank  FROM dim.dim_coal_mine_basic_info  " +
            "            <if test='flag== 3'>" +
            "  where mining_method_types='井工矿'  \n" +
            "            </if>" +
            "            <if test='flag== 4'>" +
            "  where mining_method_types='露天矿'  \n" +
            "            </if>" +
            "     GROUP BY mk_rank,mk_full_name,mk_short_name ) b " +
            "        LEFT JOIN  \n" +
            "            </if>" +

            "            <if test='level== 3'>" +
            "    dm.dm_jygl_coal_mining_daily_total_d_f d\n" +

            "            </if>" +
            "            <if test='level== 2'>" +
            "    dm.dm_jygl_coal_mining_month_total_d_f d\n" +

            "            </if>" +
            "            <if test='level== 1'>" +
            "    dm.dm_jygl_coal_mining_year_total_d_f d\n" +
            "            </if>" +


            "            <if test='type== 1'>" +
            "    LEFT JOIN " +
            "    (SELECT my_full_name,  my_short_name,my_rank  FROM dim.dim_coal_mine_basic_info\n" +
            "     GROUP BY my_rank,my_full_name,my_short_name ) b\n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    LEFT JOIN " +
            "    (SELECT mq_full_name,  mq_short_name,mq_rank  FROM dim.dim_coal_mine_basic_info\n" +
            "     GROUP BY mq_rank,mq_full_name,mq_short_name ) b\n" +
            "            </if>" +




            "            <if test='type== 1'>" +
            "     on b.my_full_name = d.org_full_name         " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "          on b.mq_full_name = d.org_full_name     " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "             on b.mk_full_name = d.org_full_name   " +
            "            </if>" +

            "WHERE  1=1 \n" +


            "            <if test='level== 3 and mark ==1'>" +
            "    and d.st_date <![CDATA[ = ]]>   current_date-1  " +
            "            </if>" +
            "            <if test='level== 3 and mark ==2'>" +
            "    and d.st_date <![CDATA[ = ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='level== 2'>" +
            "      AND d.st_month = TO_CHAR(current_date-1, 'YYYY-MM') " +
            "            </if>" +

            "            <if test='level== 1'>" +
            "       AND d.st_year = TO_CHAR(current_date-1, 'YYYY') " +
            "            </if>" +



            "            <if test='type== 1'>" +
            "              and  d.organization_type IN ('MTCY','MY')    " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "             and  d.organization_type IN ('MY','MQ')   and org_short_name  not in('北能公司','伊敏煤电','北方公司','蒙东公司')    " +
            "            </if>" +

            "            <if test='type== 3'>" +
            "              and  d.organization_type = 'MK'  and org_short_name  not in('刘园子','马蹄沟')    " +
            "            </if>" +
            "GROUP BY \n" +
            "            <if test='type== 1'>" +

            "    d.org_full_name,     \n" +
            "                d.org_short_name,if(b.my_rank is null, 0, b.my_rank) \n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    d.org_full_name,     \n" +
            "                d.org_short_name,if(b.mq_rank is null, 0, b.mq_rank) \n" +
            "            </if>" +

            "            <if test='type== 3'>" +
            "    b.mk_full_name, \n" +
            "    b.mk_short_name,b.mk_rank  \n" +
            "            </if>" +
            "ORDER BY \n" +
            "            <if test='type== 1'>" +
            "            if(b.my_rank is null, 0, b.my_rank) asc  " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "             if(b.mq_rank is null, 0, b.mq_rank) asc  " +
            "            </if>" +

            "            <if test='type== 3'>" +
            "             b.mk_rank asc  " +
            "            </if>" +
            "</script>")
    List<CoalMiningTotalVo> getPlanAndActualContrast(@Param("type") Integer type,@Param("level") Integer level,@Param("flag") Integer flag,@Param("mark") Integer mark);



    @Select("<script>" +
            "      WITH data AS (\n" +
            "    SELECT\n" +
            "       TO_CHAR(st_date, 'DD') AS st_date,  \n" +
            "                     TO_CHAR(st_date, 'MM-DD') AS stYear, \n" +
            "  CASE " +
            "                    WHEN org_short_name = '华能煤业' THEN '煤业公司' \n" +
            "                    ELSE org_short_name\n" +
            "                END AS org_short_name, " +
//            "        org_short_name,\n" +

            "        ROUND(actual_output_qty / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(actual_sales_qty / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_daily_total_d_f\n" +
            "    WHERE\n" +
            "        st_date  <![CDATA[ >= ]]>  CURRENT_DATE - INTERVAL '30 days'\n" +
            "            <if test='mark ==1'>" +
            "    and st_date <![CDATA[ <= ]]>   current_date-1  " +
            "            </if>" +
            "            <if test='mark ==2'>" +
            "    and st_date <![CDATA[ <= ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='flag== 1'>" +
            "              and organization_type = 'MY'    " +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "            and organization_type = 'MQ'  and org_short_name  not in('北能公司','伊敏煤电')    " +
            "            </if>" +
            "    \n" +
            "    UNION ALL\n" +
            "    \n" +
            "    SELECT\n" +
            "   '总计' AS st_date,\n" +
            "        TO_CHAR(st_date, 'MM-DD') AS stYear,\n" +
            "        '总计' AS org_short_name,\n" +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_daily_total_d_f\n" +
            "    WHERE\n" +
            "        st_date  <![CDATA[ >= ]]>  CURRENT_DATE - INTERVAL '30 days'\n" +
            "            <if test='mark ==1'>" +
            "    and st_date <![CDATA[ <= ]]>   current_date-1  " +
            "            </if>" +
            "            <if test='mark ==2'>" +
            "    and st_date <![CDATA[ <= ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='flag== 1'>" +
            "              and organization_type = 'MY'    " +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "            and organization_type = 'MQ'  and org_short_name  not in('北能公司','伊敏煤电')    " +
            "            </if>" +
            "    GROUP BY\n" +
            "       TO_CHAR(st_date, 'MM-DD')\n" +
            ")\n" +
            "\n" +
            "SELECT\n" +
            "    st_date,\n" +
            "    stYear,\n" +
            "    org_short_name,\n" +
            "    actualOutputQty,\n" +
            "    actualSalesQty\n" +
            "FROM\n" +
            "    data\n" +
            "ORDER BY\n" +
            "    CASE \n" +
            "        WHEN org_short_name = '总计' THEN 1\n" +
            "        WHEN org_short_name = '华能煤业' THEN 2\n" +
            "        WHEN org_short_name = '北方公司' THEN 3\n" +
            "        WHEN org_short_name = '蒙东公司' THEN 4" +

            "        WHEN org_short_name = '华亭' THEN 2\n" +
            "        WHEN org_short_name = '扎煤' THEN 3\n" +
            "        WHEN org_short_name = '陕西' THEN 4\n" +
            "        WHEN org_short_name = '庆阳' THEN 5\n" +
            "        ELSE 6 " +
            "    END," +
            "    stYear DESC;\n " +
            "</script>")
    List<CoalMiningTotalVo> recentlyThirtyDaysByIndustry(@Param("flag") Integer flag,@Param("mark") Integer mark);


    @Select("<script>" +
            "  WITH DATA AS (\n" +
            "    SELECT\n" +
            "        st_year AS stYear,\n" +
            "  CASE " +
            "                    WHEN org_short_name = '华能煤业' THEN '煤业公司' \n" +
            "                    WHEN org_short_name = '滇东矿业' THEN  '滇东' \n" +
            "                    WHEN org_short_name = '华亭煤业' THEN  '华亭'\n" +
            "                    WHEN org_short_name = '扎煤公司' THEN  '扎煤'\n" +
            "                    WHEN org_short_name = '庆阳煤电' THEN  '庆阳'\n" +
            "                    WHEN org_short_name = '陕西矿业' THEN  '陕西'\n" +
            "                    ELSE org_short_name \n" +
            "  END AS org_short_name, " +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_year_total_d_f\n" +
            "    WHERE\n" +
            "        st_year::INTEGER <![CDATA[ >= ]]> EXTRACT(YEAR FROM CURRENT_DATE) - 10\n" +
            "        AND st_year::INTEGER <![CDATA[ < ]]> EXTRACT(YEAR FROM CURRENT_DATE)\n" +
            "            <if test='flag== 1'>" +
            "              and organization_type = 'MY'  " +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "            and organization_type = 'MQ'   and org_short_name  not in('北能公司','伊敏煤电')    " +
            "            </if>" +
            "    GROUP BY\n" +
            "        st_year,\n" +
            "  CASE \n" +
            "            WHEN org_short_name = '华能煤业' THEN '煤业公司'  \n" +
            "            WHEN org_short_name = '滇东矿业' THEN '滇东' \n" +
            "            WHEN org_short_name = '华亭煤业' THEN '华亭'\n" +
            "            WHEN org_short_name = '扎煤公司' THEN '扎煤'\n" +
            "            WHEN org_short_name = '庆阳煤电' THEN '庆阳'\n" +
            "            WHEN org_short_name = '陕西矿业' THEN '陕西'\n" +
            "            ELSE org_short_name  " +
//            "        CASE\n" +
//            "            \n" +
//            "            ELSE org_short_name\n" +
            "        END\n" +
            "    UNION ALL\n" +
            "    SELECT\n" +
            "        st_year AS stYear,\n" +
            "        '总计' AS org_short_name,\n" +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_year_total_d_f\n" +
            "    WHERE\n" +
            "        st_year::INTEGER <![CDATA[ >= ]]> EXTRACT(YEAR FROM CURRENT_DATE) - 10\n" +
            "        AND st_year::INTEGER <![CDATA[ < ]]> EXTRACT(YEAR FROM CURRENT_DATE)\n" +
            "            <if test='flag== 1'>" +
            "              and organization_type = 'MY'  " +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "            and organization_type = 'MQ'   and org_short_name  not in('北能公司','伊敏煤电')    " +
            "            </if>" +
            "    GROUP BY\n" +
            "        st_year\n" +
            ")\n" +
            "SELECT\n" +
            "    stYear,\n" +
            "    org_short_name,\n" +
            "    actualOutputQty,\n" +
            "    actualSalesQty\n" +
            "FROM\n" +
            "    DATA\n" +
            "ORDER BY\n" +
            "    CASE\n" +
            "        WHEN org_short_name = '总计' THEN 1\n" +
            "        WHEN org_short_name = '华能煤业' THEN 2\n" +
            "        WHEN org_short_name = '北方公司' THEN 3\n" +
            "        WHEN org_short_name = '蒙东公司' THEN 4\n" +
            "        WHEN org_short_name = '华亭' THEN 2\n" +
            "        WHEN org_short_name = '扎煤' THEN 3\n" +
            "        WHEN org_short_name = '陕西' THEN 4\n" +
            "        WHEN org_short_name = '庆阳' THEN 5\n" +
            "        ELSE 6\n" +
            "    END,\n" +
            "    stYear DESC;\n " +
            "</script>")
    List<CoalMiningTotalVo> recentlyTenYearsByIndustry(@Param("flag") Integer flag);

    @Select("<script>" +
            "    WITH data AS (\n" +
            "    SELECT\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'MM') AS st_date,\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'YYYY-MM') AS stYear,\n" +
//            "        org_short_name,\n" +
            "  CASE " +
            "                    WHEN org_short_name = '华能煤业' THEN '煤业公司' \n" +
            "                    WHEN org_short_name = '滇东矿业' THEN  '滇东' \n" +
            "                    WHEN org_short_name = '华亭煤业' THEN  '华亭'\n" +
            "                    WHEN org_short_name = '扎煤公司' THEN  '扎煤'\n" +
            "                    WHEN org_short_name = '庆阳煤电' THEN  '庆阳'\n" +
            "                    WHEN org_short_name = '陕西矿业' THEN  '陕西'\n" +
            "                    ELSE org_short_name \n" +
            "  END AS org_short_name , " +
            "        ROUND(actual_output_qty / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(actual_sales_qty / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_month_total_d_f\n" +
            "    WHERE\n" +
            "        CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ >= ]]> DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'\n" +
            "        AND CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE)\n" +
            "            <if test='flag== 1'>" +
            "              and organization_type = 'MY'  " +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "            and organization_type = 'MQ'   and org_short_name  not in('北能公司','伊敏煤电')    " +
            "            </if>" +
            "    \n" +
            "    UNION ALL\n" +
            "    \n" +
            "    SELECT\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'MM') AS st_date,\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'YYYY-MM') AS stYear,\n" +
            "        '总计' AS org_short_name,\n" +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_month_total_d_f\n" +
            "    WHERE\n" +
            "        CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ >= ]]> DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'\n" +
            "        AND CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE)\n" +
            "            <if test='flag== 1'>" +
            "              and organization_type = 'MY'  " +
            "            </if>" +
            "            <if test='flag== 2'>" +
            "            and organization_type = 'MQ'   and org_short_name  not in('北能公司','伊敏煤电')    " +
            "            </if>" +
            "    GROUP BY\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'YYYY-MM'),\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'MM')\n" +
            ")\n" +
            "\n" +
            "SELECT\n" +
            "    st_date,\n" +
            "    stYear,\n" +
            "    org_short_name,\n" +
            "    actualOutputQty,\n" +
            "    actualSalesQty\n" +
            "FROM\n" +
            "    data\n" +
            "ORDER BY\n" +
            "    CASE \n" +
            "        WHEN org_short_name = '总计' THEN 1\n" +
            "        WHEN org_short_name = '华能煤业' THEN 2\n" +
            "        WHEN org_short_name = '北方公司' THEN 3\n" +
            "        WHEN org_short_name = '蒙东公司' THEN 4" +
            "        WHEN org_short_name = '华亭' THEN 2\n" +
            "        WHEN org_short_name = '扎煤' THEN 3\n" +
            "        WHEN org_short_name = '陕西' THEN 4\n" +
            "        WHEN org_short_name = '庆阳' THEN 5\n" +
            "        ELSE 6 " +
            "    END,\n" +
            "    stYear DESC; " +
            "</script>")
    List<CoalMiningTotalVo> recentlyTwelveMonthsByIndustry(@Param("flag") Integer flag,@Param("mark") Integer mark);

//    @Select("<script>" +
//            "    SELECT \n" +
//            "    mk_code,\n" +
//            "    mk_short_name,\n" +
//            "    mk_full_name,\n" +
//            "    tracking_report,\n" +
//            "    work_goal,\n" +
//            "    detailed_requirements,\n" +
//            "    reporter,\n" +
//            "    report_time,\n" +
//            "    last_update_time\n" +
//            "FROM \n" +
//            "    dws.dws_jygl_key_work_tracking_info\n" +
//            "WHERE \n" +
//            "    DATE(report_time) = CURRENT_DATE\n" +
//            "ORDER BY \n" +
//            "    last_update_time DESC;" +
//            "</script>")
@Select("<script>" +
        " SELECT  " +
        "  infoo.mk_code," +
        " infoo.mk_short_name," +
        " infoo.mk_full_name," +
        " infoo.mq_full_name," +
        " infoo.mq_short_name," +
        " infoo.mq_code," +
        " infoo.my_full_name," +
        " infoo.my_short_name," +
        " infoo.my_code," +
        " worko.tracking_report," +
        " worko.work_goal," +
        " worko.detailed_requirements," +
        " worko.reporter," +
        " worko.report_time," +
        " worko.last_update_time " +
        " FROM  dim.dim_coal_mine_basic_info infoo left join dws.dws_jygl_key_work_tracking_info worko on infoo.mk_code = worko.mk_code  " +
        " where  " +
        " DATE(worko.report_time) <![CDATA[ = ]]> CURRENT_DATE  " +
        " <if test='orgCode != null and orgCode != \"\" '>"+
        "  and infoo.mq_code <![CDATA[ = ]]> #{orgCode} " +
        " </if>"+
        " <if test='code != null and code != \"\" '>"+
        " and infoo.mk_code <![CDATA[ = ]]> #{code}\n" +
        " </if>"+
        " ORDER BY " +
        " worko.last_update_time DESC; " +
       "</script>")
    List<WorkTrackingInfoVo> getWorkTrackingInfo(@Param("orgCode") String orgCode, @Param("code") String code);


    @Select("<script>" +
            "   SELECT org_code, org_full_name, ROUND((coal_stock_qty) / 10000.0, 2) AS coal_stock_qty \n" +
            "FROM dm_jygl_coal_mining_daily_total_d_f\n" +
            "WHERE \n" +
            "  coal_stock_qty <![CDATA[ > ]]>'0' \n " +
            "            <if test='mark == 1' >" +
            "    and st_date <![CDATA[ = ]]>   current_date-1 " +
            "            </if>" +
            "            <if test='mark == 2' >" +
            "    and st_date <![CDATA[ = ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='type== 1'>" +
            "              and organization_type = 'MTCY'    " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "            and organization_type = 'MY'    " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "            and organization_type = 'MK'    " +
            "            </if>" +
            "</script>")
    List<CoalMiningTotalVo> getStock(@Param("type") Integer type,@Param("mark") Integer mark);

    @Select("<script>" +
            "   " +
            "\t\tSELECT org_code,org_full_name,org_short_name,\n" +

            "            <if test='type== 1'>" +
            " TO_CHAR(COALESCE(CAST(output_qty_yoy AS numeric), 0), 'FM9999999990.00') AS outputQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_yoy AS numeric), 0), 'FM9999999990.00') AS salesQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_yoy AS numeric), 0), 'FM9999999990.00') AS footageQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_yoy AS numeric), 0), 'FM9999999990.00') AS peelQtyYoy " +
//            "             output_qty_yoy,sales_qty_yoy,footage_qty_yoy, peel_qty_yoy    " +
            "            </if>" +
            "            <if test='type== 2'>" +
//            "            output_qty_mom,sales_qty_mom,footage_qty_mom,peel_qty_mom," +
//            " output_qty_yoy,sales_qty_yoy,footage_qty_yoy, peel_qty_yoy    " +
            "    TO_CHAR(COALESCE(CAST(output_qty_mom AS numeric), 0), 'FM9999999990.00') AS outputQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_mom AS numeric), 0), 'FM9999999990.00') AS salesQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_mom AS numeric), 0), 'FM9999999990.00') AS footageQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_mom AS numeric), 0), 'FM9999999990.00') AS peelQtyMom,\n" +
            "   TO_CHAR(COALESCE(CAST(output_qty_yoy AS numeric), 0), 'FM9999999990.00') AS outputQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_yoy AS numeric), 0), 'FM9999999990.00') AS salesQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_yoy AS numeric), 0), 'FM9999999990.00') AS footageQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_yoy AS numeric), 0), 'FM9999999990.00') AS peelQtyYoy " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "     TO_CHAR(COALESCE(CAST(output_qty_mom AS numeric), 0), 'FM9999999990.00') AS outputQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_mom AS numeric), 0), 'FM9999999990.00') AS salesQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_mom AS numeric), 0), 'FM9999999990.00') AS footageQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_mom AS numeric), 0), 'FM9999999990.00') AS peelQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(output_qty_yoy AS numeric), 0), 'FM9999999990.00') AS outputQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_yoy AS numeric), 0), 'FM9999999990.00') AS salesQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_yoy AS numeric), 0), 'FM9999999990.00') AS footageQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_yoy AS numeric), 0), 'FM9999999990.00') AS peelQtyYoy " +
//            "            output_qty_mom,sales_qty_mom,footage_qty_mom,peel_qty_mom," +
//            " output_qty_yoy,sales_qty_yoy,footage_qty_yoy, peel_qty_yoy    " +
            "            </if>" +

            " FROM " +
            " \n" +
            "            <if test='type== 1'>" +
            "              dws_jygl_coal_mining_year_yoy_d_f    " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "            dws_jygl_coal_mining_month_yoy_mom_d_f   " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "            dws_jygl_coal_mining_daily_yoy_mom_d_f   " +
            "            </if>" +
            " WHERE  organization_type = 'MTCY' " +


            "            <if test='mark == 1' >" +
            "    and dt = CURRENT_DATE - 1 " +
            "            </if>" +
            "            <if test='mark == 2' >" +
            "    and dt = CURRENT_DATE - 2 " +
            "            </if>" +
            "   " +

            "</script>")
    List<CoalMiningYoyMomVo> getSameRingCompare(@Param("type") Integer type, @Param("mark") Integer mark);

    @Select("<script>" +
            "  select \n" +
            "    my_full_name,\n" +
            "    my_short_name,\n" +
            "    my_code,\n" +
            "    mq_full_name,\n" +
            "    mq_short_name,\n" +
            "    mq_code,\n" +
            "    mk_full_name,\n" +
            "    mk_short_name,\n" +
            "    mk_code,\n" +
            "    approved_prod_capacity_qty,\n" +
            "    approved_person_capacity_qty,\n" +
            "    mine_operation_status_desc,\n" +
            "    mine_disaster_types,\n" +
            "    mine_development_types,\n" +
            "    mining_method_types,\n" +
            "    mk_rank\n" +
            " from dim_coal_mine_basic_info order by  mk_rank " +
            "</script>")
    List<MineInfoVo> getMineInfo(@Param("type") Integer type);



    @Select("<script>" +
            "  select \n" +
            "    mk_full_name,\n" +
            "    mk_short_name,\n" +
            "    mk_code,\n" +
            "    safety_production_days as safetyProductionDays \n" +
            " from dws.dws_jygl_safety_production_days_total  where mk_code not in('621022009406','620824009852') " +
            "</script>")
    List<MineInfoVo> getSafetyMineInfo(@Param("type") String type);

    @Select("<script>" +
            "SELECT " +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "    mq_full_name," +
            "    mq_short_name," +
            "    mq_code," +
            " </if>"+
            "    COALESCE(SUM(approved_prod_capacity_qty), 0) AS approved_prod_capacity_qty " +
            " FROM  " +
            "    dim.dim_coal_mine_basic_info where mk_short_name  not in('刘园子','马蹄沟','雨汪一井','白龙山一井')  " +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "  and  mq_code  <![CDATA[ = ]]> #{companyCode} " +
            " GROUP BY  " +
            "    mq_full_name, mq_short_name, mq_code;" +
            " </if>"+
            "</script>")
    List<MineInfoVo> getMineProdCapacity(@Param("companyCode") String  companyCode);

    @Select("<script>" +
            " SELECT " +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "    mq_full_name," +
            "    mq_short_name," +
            "    mq_code," +
            " </if>"+
            "    COALESCE(SUM(mining_workingface_qty), 0)  as miningWorkingfaceQty,\n" +
            "    COALESCE(SUM(tunnel_workingface_qty), 0)  as tunnelWorkingfaceQty,\n" +
            "    COALESCE(SUM(pro_intelligent_mining_workingface_qty), 0)  as proIntelligentMiningWorkingfaceQty,\n" +
            "    COALESCE(SUM(pro_intelligent_tunnel_workingface_qty), 0)  as proIntelligentTunnelWorkingfaceQty \n" +
            " FROM  " +
            "    dim.dim_coal_mine_basic_disaster_intelligent_info where 1=1  \n" +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "  and  mq_code  <![CDATA[ = ]]> #{companyCode} " +
            " GROUP BY  " +
            "    mq_full_name, mq_short_name, mq_code;" +
            " </if>"+
            "</script>")
    List<MineInfoVo> getMineWorkingFaceStatus(@Param("companyCode") String  companyCode);

    @Select("<script>" +
            " select mk_full_name as mkFullName,workingface_name as workingFaceName " +
            " from dim.dim_coal_mine_workingface_basic_info " +
            " where mk_full_name = #{code} " +
            " group by  mk_full_name,workingface_name \n " +
            "</script>")
    List<MineInfoVo> getDrivingWorkingFace(@Param("code") String code);
    @Select("<script>" +
            "     select wbi.mk_full_name,wbi.mk_code , bi.mk_rank from dim.dim_coal_mine_basic_info bi LEFT JOIN  dim.dim_coal_mine_workingface_basic_info wbi on bi.mk_full_name = wbi.mk_full_name  \n" +
            "        where bi.mining_method_types = #{type}\n" +
            "   AND wbi.mk_full_name IS NOT NULL\n" +
            "  AND wbi.mk_code IS NOT NULL\n" +
            "  AND bi.mk_rank IS NOT NULL " +
            "        group by  wbi.mk_full_name,wbi.mk_code,bi.mk_rank order by bi.mk_rank \n " +
            "</script>")
    List<MineInfoVo> getDrivingWorkingFaceMine(@Param("type") String type);

    @Select("<script>" +
            " <if test='type== 2'>" +
            "   WITH date_range AS (\n" +
            "    SELECT generate_series(\n" +
            "        CURRENT_DATE - INTERVAL '30 days',  \n" +
            "            <if test='mark == 2' >" +
            "        CURRENT_DATE - INTERVAL '2 day',     \n" +
            "            </if>" +
            "            <if test='mark == 1' >" +
            "        CURRENT_DATE - INTERVAL '1 day',     \n" +
            "            </if>" +
            "        INTERVAL '1 day'                    \n" +
            "    )::DATE AS day\n" +
            ")\n" +
            "\n" +

            "SELECT\n" +
            "     TO_CHAR(dr.day, 'DD') AS date,   \n" +
            "\t\tworkingface_name,\n" +
            "  <if test='flag== 1'>" +
            "     TO_CHAR(COALESCE(d.dailyadvance, 0), 'FM9999999990.00') AS dailyadvance \n" +
            "            </if>" +
            "  <if test='flag== 2'>" +
            "     TO_CHAR(COALESCE(d.dailyadvance  / 10000, 0), 'FM9999999990.00') AS dailyadvance \n" +
            "            </if>" +
            " FROM\n" +
            "    date_range dr\n" +
            "LEFT JOIN\n" +
            "    dm_jygl_workingface_footage_daily_d_f d\n" +
            "    ON dr.day = d.daily_time\n" +
            "    AND d.workingface_name = #{WorkingFace} " +
            "    AND d.organization_type = 'MK'  " +
            "            <if test='mark == 2' >" +
            "  and d.daily_time <![CDATA[ <= ]]>   current_date-2 and d.dt = current_date-2 " +
            "            </if>" +
            "            <if test='mark == 1' >" +
            "  and d.daily_time <![CDATA[ <= ]]>   current_date-1 and d.dt = current_date-1 " +
            "            </if>" +
            "  " +
            " ORDER BY\n" +
            "    dr.day; " +
            "            </if>" +


            "  <if test='type== 1'>" +
            " \n" +
            "   " +
            "WITH months AS (\n" +
            "    SELECT \n" +
            "        to_char(date_trunc('month', current_date) - interval '1 month' * i, 'YYYY-MM') AS st_month\n" +
            "    FROM generate_series(1, 12) i  \n" +
            ")\n" +
            "SELECT \n" +
            "    TO_CHAR(TO_DATE(m.st_month, 'YYYY-MM'), 'MM') AS date,\n" +
            "    COALESCE(d.workingface_name, #{WorkingFace}) AS workingface_name,\n" +
            "  <if test='flag== 1'>" +
            "     TO_CHAR(COALESCE(d.dailyadvance, 0), 'FM9999999990.00') AS dailyadvance,\n" +
            "            </if>" +
            "  <if test='flag== 2'>" +
            "     TO_CHAR(COALESCE(d.dailyadvance  / 10000, 0), 'FM9999999990.00') AS dailyadvance,\n" +
            "            </if>" +
            "    COALESCE(d.organization_type, 'MK') AS organization_type\n" +
            "FROM months m\n" +
            "LEFT JOIN dm_jygl_workingface_footage_month_d_f d\n" +
            "    ON m.st_month = d.st_month\n" +
            "    AND d.workingface_name = #{WorkingFace}\n" +
            "    AND d.organization_type = 'MK'\n" +
//            " AND d.st_month = TO_CHAR(current_date, 'YYYY-MM') " +
            " and d.dt = current_date-1 " +
            " ORDER BY m.st_month;\n " +
            "            </if>" +
            "</script>")
    List<MineInfoVo> getTrendDrivingAndStripped(@Param("Name") String Name,@Param("type") Integer type,@Param("WorkingFace") String WorkingFace,@Param("mark") Integer mark,@Param("flag") Integer flag);

    @Select("<script>" +
            " select\n" +
            " date,\n" +
            "    score,\n" +
            "    rank,\n" +
            "    id,\n" +
            "    sysOrgCode_dictText," +
            "   CASE\n" +
            "        WHEN departNameAbbr = '陕西矿业分公司' THEN '陕西'\n" +
            "        WHEN departNameAbbr = '扎煤公司' THEN '扎煤'\n" +
            "        WHEN departNameAbbr = '滇东矿业' THEN '滇东'\n" +
            "        WHEN departNameAbbr = '华亭煤业' THEN '华亭'\n" +
            "        WHEN departNameAbbr = '庆阳煤电' THEN '庆阳'\n" +
            "        ELSE ''\n" +
            "    END AS departNameAbbr," +
            "    totalScore,\n" +
            "    createBy,\n" +
            "    hide,\n" +
            "    createTime,\n" +
            "    underreporting,\n" +
            "    sysOrgCode,\n" +
            "    remarks\n" +
            " from environment_ranking order by rank " +
            "</script>")
    List<EnvironmentRankingVo> getEnvironmentalMonthlyAssessmentRanking();

    @Select("<script>" +
            " select online_time_total,effective_rate,sys_org_code,sys_org_code_dict_text from efficient_transmission where effective_rate <![CDATA[ > ]]> '0' " +
            "</script>")
    List<EfficientTransmissionVo> getEnvironmentalEfficientTransmission();


    @Select("<script>" +
            " \tSELECT \n" +
            "    '生产矿井' AS mineName,\n" +
            "    COALESCE(COUNT(*), 0) AS mineNumber\n" +
            "FROM \n" +
            "    dim.dim_coal_mine_basic_info\n" +
            "WHERE \n" +
            "    mine_operation_status_desc = '生产'\n" +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "  and mq_code   <![CDATA[ = ]]> #{companyCode} " +
            " </if>"+
            "\n" +
            "UNION ALL\n" +
            "\n" +
            "SELECT \n" +
            "    '基建矿井' AS mineName,\n" +
            "    COALESCE(COUNT(*), 0) AS mineNumber\n" +
            "FROM \n" +
            "    dim.dim_coal_mine_basic_info\n" +
            "WHERE \n" +
            "    mine_operation_status_desc = '基建'\n" +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "  and mq_code   <![CDATA[ = ]]> #{companyCode} " +
            " </if>"+
            "\n" +
            "UNION ALL\n" +
            "\n" +
            "SELECT \n" +
            "    '停产矿井' AS mineName,  " +
            "    COALESCE(COUNT(*), 0) AS mineNumber\n" +
            "FROM \n" +
            "    dim.dim_coal_mine_basic_info\n" +
            "WHERE \n" +
            "    mine_operation_status_desc = '停产'\n" +
            " <if test='companyCode != null and companyCode != \"\" '>"+
            "  and mq_code  <![CDATA[ = ]]> #{companyCode} " +
            " </if>"+
            "</script>")
    List<MineNumberVo> getMineNumber(@Param("companyCode") String companyCode);
}
